using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using SqlCeDriver;
using System.Data.SqlServerCe;
using DbViewDriver;

/*
	This is an add-on to the Sql CE 3.5 driver to cope with the different versions of the assembly with the same
	name used for the Sql CE 4 engine. I expect there is a better way, but I wanted to prove the concept before
	researching the right way to do it.

	So all engine access is isolated to abstract class SqlCEDataAccessor, each version of Sql Ce provides its
	own implementation and SqlCe4DataSource inherits from the base 3.5 driver to override only the necessary
	parts
*/
namespace SqlCe35Driver
{
	// class loaded by the plugin loader.
	public class SqlCe35DataSource : SqlCeDataSource, DbViewDriver.IDataSource //, DbViewDriver.IScripting, DbViewDriver.ISqlOperations
	{
		static DbViewDriver.IDataSource g_source = new SqlCe35DataSource();
		public static DbViewDriver.IDataSource Source { get { return g_source; } }

		public override SqlCEDataAccessor GetAccessor() { return new SqlCEDataAccessor35(); }

		protected override String DriverNameInternal {	get { return "Sql Server Compact 3.5"; } }
		protected override Version VersionInternal {	get { return Version.SqlCE35; } }

		public void Dispose() { }

		///////////////////////////////////////////////////////////////////////////
		// Interface
		public bool PerformDriverCheck(out String configErrorMessage)
		{ 
			return PerformDriverCheck_Impl(out configErrorMessage);
		}
		// Property to use in selection lists. This in base class as it i
		public String DriverName { get { return DriverNameInternal; } }

		// Database connection string property  
		//public String ConnectionString { get { return this.GetConnectionString(); } }

		// new one: Development database
		public bool IsDevelopmentDatabase{ get { return false; } } // experiment with true at some point

		// Set connection from configuration
		public void SetCurrentConnection(DbView.ConnectionInfo connectionInfo)
		{
			this.SetCurrentConnection_Impl(connectionInfo);
		}

		// Configure driver. Return T if the current connection was reset
		public bool ConfigureConnection(IConnectionConfig connectionConfig)
		{
			return ConfigureConnection_Impl(connectionConfig);
		}

		// Supports the given command
		public bool SupportsCommand(DbView.Commands.Cmnd command)
		{
			return SupportsCommand_Impl(command);
		}

		// populate the datatable from a command string
		public DataTable BuildTable(DbView.Command command, DbView.ViewContext context)
		{
			return BuildTable_Impl(command, context);
		}

		// Return the source for objects in a single string
		public String GetSourceCode(DbView.Command command)
		{
			return GetSourceCode_Impl(command);
		}

		// preferred version. Will hopfully remove the other 
		public String GetSourceCode(DbView.ViewContext context, String objectName)
		{
			return GetSourceCode_Impl(context, objectName);
		}

		public DbView.Command ExecuteDriverAction(DbView.Command command, object window,
											DbViewDriver.IConnectionConfig connections,
											String searchText, out bool refreshGrid)
		{
			return ExecuteDriverAction_Impl(command, window, connections, searchText, out refreshGrid);
		}

		// Update data in the table. Return t/f success fail with details in statusMessage
		public bool Update(DataTable table, out String statusMessage)
		{
			return Update_Impl(table, out statusMessage);
		}

		// populate the datatable from a command string
		public void GetTableSchema(String tableName, out DataTable table, bool full)
		{
			GetTableSchema_Impl(tableName, out table, full);
		}
		// Does the driver implement non standards commands? If so 
		// it should define a Custom Menu structure
		// but return null if this is not applicable
		public CustomMenuItem BuildCustomMenu()
		{
			return BuildCustomMenu_Impl();
		}

		// supply right-click popups for the given command. Ok to return null
		public CustomMenuItem BuildPopupMenu(DbView.Command command)
		{
			return BuildPopupMenu_Impl(command);
		}

		public DbViewDriver.IDatabaseExportSource DatabaseExportSource 
		{
			get{ return new SqlCEImportExportInterfaceImplementation(this.GetAccessor()); }
			set {} // don't bother when 
		}

		// Supplies a scripter for certain ops. If null scripting is unsupported.
		//public IScripting Scripter { get {return null;} }
		////public ISqlOperations SqlOps { get {return null; } }

		public object TryToGetOptionalInterface(DbViewDriver.OptionalInterface requestedInterface)
		{
			if (requestedInterface == DbViewDriver.OptionalInterface.Export)
				return this.DatabaseExportSource;
			if (requestedInterface == DbViewDriver.OptionalInterface.Schema ||
				requestedInterface == DbViewDriver.OptionalInterface.Lookup)
				return new SqlCEOptionInterfaceImplementations(this.GetAccessor(), this);
			return null;
		}

	}
	
	// encapulate use of the Sql Ce 4.0 assembly.
	class SqlCEDataAccessor35 : SqlCEDataAccessor
	{
		public override DataTable Query(String strConnectionString, String[] commandList, String tableName)
		{
			DataSet dset = new DataSet();
			DataTable dt = new DataTable();
			String command = "";
			using (SqlCeConnection con = new SqlCeConnection(strConnectionString))
			{
				try
				{
					con.Open();
					for (int idx = 0; idx < commandList.GetLength(0); ++idx)
					{
						command = commandList[idx];
						if (command.Trim().Length > 2)
						{
							if (IsQuery(command))
							{
								SqlCeDataAdapter da = new SqlCeDataAdapter(command, con);
								// execute into something
								da.Fill(dset, tableName);
								if (dset.Tables.Count > 0)
									dt = dset.Tables[0];
							}
							else
							{
								SqlCeCommand cmd = new SqlCeCommand(command, con);
								int rows = cmd.ExecuteNonQuery();
							}
						}
					}
				}
				catch (SqlCeException sqlexception)
				{
					String message = String.Format("Sql Error: {0}\n" +
												   "connection: {1}\n" +
													"Sql: {2}\n",
													sqlexception.Message, strConnectionString, command);
					Exception ex = new Exception(message);
					throw ex; // abort process having logged as much as possible.
				}
				finally
				{
					con.Close();
				}
			}
			return dt;
		}

		// do you get a data set back or not?
		private bool IsQuery(string sql)
		{
			return (sql.ToUpper().IndexOf("SELECT ") != -1);
		}

		public override int Update(String strConnectionString, String referenceSql, DataTable updatedTable, out String statusMessage)
		{
			int status = 0;
			statusMessage = "ok";
			try
			{
				// do it.
				SqlCeDataAdapter myDataAdapter = new SqlCeDataAdapter();
				// the sql and the command builder automatically generate the 
				// insert/update/delete sql
				using (SqlCeConnection connection = new SqlCeConnection(strConnectionString))
				{
					myDataAdapter.SelectCommand = new SqlCeCommand(referenceSql, connection);
					SqlCeCommandBuilder cb = new SqlCeCommandBuilder(myDataAdapter);

					connection.Open();

					// Without the SqlCommandBuilder this line would fail
					// below apparently checks the state of each row in the table
					// and applies the correct sql to update it.
					myDataAdapter.Update(updatedTable);
					connection.Close();
					status = 1;
				}
			}
			catch (DBConcurrencyException e)
			{
				statusMessage = e.Message;
				status = -3;
				//MessageBox.Show(message);
			}
			catch (InvalidOperationException e)
			{
				statusMessage = e.Message;
				status = -2;
			}
			catch (SqlCeException e)
			{
				statusMessage = e.Message;
				status = -1;
			}
			return status;
		}

		public override void CreateDatabaseFile(String strConnectionString)
		{
			SqlCeEngine en = new SqlCeEngine(strConnectionString);
			en.CreateDatabase();
		}

		public override void ResetPassword(String oldConnStr, String newConnStr)
		{
			SqlCeEngine engine = new SqlCeEngine(oldConnStr);
			engine.Compact(null);
			engine.Compact(newConnStr);
		}

		public override void UpgradeDatabase(String connStr)
		{
			using (SqlCeEngine engine = new SqlCeEngine(connStr))
			{
				// can throw I bet
				engine.Upgrade();
			}
		}
		
		public override void AccessDatabaseEngineType()
		{
			// it's enough to attempt to construct one.
			using (SqlCeConnection sqlConn = new SqlCeConnection())
			{
			}
		}
/*
	From here down is a duplication of the code in the CE4.0 driver. The duplication is necessary because the difference versions
	of the driver reference different DLLS unless there is a way I don't know about
*/
		// has to be here as it requires access to versioned objects.
		public override void CopyTable(String sourceConnection, String sourceTable, String tgtConnection, String tgtTable, String autoIncField, bool copyContent, ShowProgressDelegate progress)
		{
			SqlCeConnection connSource = new  SqlCeConnection();
			SqlCeConnection connTgt = new  SqlCeConnection();
			try
			{
				// Open the connection and create a SQL command
				//
				connSource = new SqlCeConnection(sourceConnection);
				connSource.Open();

				SqlCeCommand cmda = new SqlCeCommand("SELECT count(*) FROM " + sourceTable, connSource);
				SqlCeDataReader  rdra = cmda.ExecuteReader();
				rdra.Read();
				int count = (int)rdra[0];

				SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM " + sourceTable, connSource);
				SqlCeDataReader  rdr = cmd.ExecuteReader();

				if (sourceConnection == tgtConnection)
					connTgt = connSource;
				else
				{
					connTgt = new SqlCeConnection(tgtConnection);
					connTgt.Open();
				}
				// check existance of tgt
				SqlCeCommand cmd2 = new SqlCeCommand();
				cmd2.Connection = connTgt;
				cmd2.CommandText = tgtTable;
				cmd2.CommandType = CommandType.TableDirect;
				SqlCeCommand cmd3 = new SqlCeCommand(String.Format("set IDENTITY_INSERT [{0}] ON; ",tgtTable), connTgt);
				int max = -1;
				if (autoIncField.Length > 0)
				{
					cmd3.ExecuteNonQuery();
					max = 1;
				}
				SqlCeResultSet rst = cmd2.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
				// initialise progress counters.
				int done = 0;
				bool userCancel = false;
				// Iterate through the results
				//
				while (rdr.Read())
				{
					SqlCeUpdatableRecord rec = rst.CreateRecord();
					for (int idx = 0; idx < rdr.FieldCount; ++idx)
					{
						rec[idx] = rdr[idx];
					}
					rst.Insert(rec);
					if (max > -1)
					{
						int val = (Int32)rec[autoIncField];
						if (val > max) max = val;
					}
					if ((done % 1000) == 0)
						userCancel = progress(count, done);
					++done;
				}
				// Always dispose data readers and commands as soon as practicable
				//
				rst.Close();
				rdr.Close();
				cmd.Dispose();
				cmd2.Dispose();

				if (autoIncField.Length > 0)
				{
				    cmd3.CommandText = String.Format("set IDENTITY_INSERT [{0}] OFF; ",tgtTable);
				    cmd3.ExecuteNonQuery();
				    cmd3.Dispose();
				    //cmd3.CommandText = String.Format("alter table [{0}] ALTER COLUMN {1} IDENTITY ({2},1); ",tgtTable, autoIncField, max);
				    //cmd3.ExecuteNonQuery();
				    new Helper(this).ResetAutoIdentity(tgtConnection, tgtTable);
				}
				userCancel = progress(count, count);
			}
			finally
			{
				// Close the connection when no longer needed
				//
				connSource.Close();
				connTgt.Close();
			}
		}
		
		public override void PopulateTable(String connectionString, String tableName, DataTable data, String autoIncField, bool isStaging)
		{
			SqlCeCommand cmd2 = new SqlCeCommand();
			cmd2.Connection = new SqlCeConnection(connectionString);
            cmd2.Connection.Open();
			// Establish identity field and adjust if necessary
			if (autoIncField.Length > 0)
			{
				cmd2.CommandText = "set IDENTITY_INSERT "+tableName+" ON";
				cmd2.ExecuteNonQuery();
			}
			// Use TableDirect for speed and to preserve identity values.
			cmd2.CommandText = tableName;
			cmd2.CommandType = CommandType.TableDirect;
			SqlCeResultSet rst = null;
			try
			{
				rst = cmd2.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
				// Iterate through the results
				//
				foreach (DataRow dr in data.Rows)
				{
					SqlCeUpdatableRecord rec = rst.CreateRecord();
					for (int idx = 0; idx < data.Columns.Count; ++idx)
					{
						rec[idx] = dr[idx];
					}
					rst.Insert(rec);
				}
				// Always dispose data readers and commands as soon as practicable
				//
			}
			catch (Exception)
			{
				// forward all exceptions to caller. Try / Catch is here for resource tidying in finally
				throw;
			}
			finally
			{
				rst.Close();
	 			cmd2.Dispose();
			}
			// fix up the next autoincrement value.
			if (autoIncField.Length > 0)
				new Helper(this).ResetAutoIdentity(connectionString, tableName);
		}

		// routine to update a target table from a staging table
		// just the primary key matching fields.
		public override void UpdateTable(String connectionString, String targetName, String stagingName, String[] pkeys, String[] updateFields)
		{
			// fetching sql
			// select pkeys, update fields from t1 join t2 on pkey list where not ( update fields equal list)  order by pkey list
			// The template is used twice: once to get all target table data. Next all staging table data.
			String template = @"
select {2}, {3} 
  from {0}
  join {1} on {4}
 where {5}
order by {2}
";
			// Open a SQL CE session
			SqlCeCommand cmd = new SqlCeCommand();
			cmd.Connection = new SqlCeConnection(connectionString);
            cmd.Connection.Open();
			// get data from target tables for update
			cmd.CommandText = String.Format(template, Helper.SafeTableName(targetName), Helper.SafeTableName(stagingName), 
							MakePKListSqlPart(pkeys, targetName),
							MakeUpdateListSqlPart(updateFields, targetName),
							MakeJoinClauseSqlPart(pkeys, targetName, stagingName),
							MakeInequalityTestSqlPart(updateFields, targetName, stagingName));

			// get an updatable record set from the target table
			cmd.CommandType = CommandType.Text;
			SqlCeResultSet rstTarget = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
			// get data from the staging table to update the target table
			cmd.CommandText = String.Format(template, Helper.SafeTableName(stagingName), Helper.SafeTableName(targetName), 
							MakePKListSqlPart(pkeys, stagingName),
							MakeUpdateListSqlPart(updateFields, stagingName),
							MakeJoinClauseSqlPart(pkeys, stagingName, targetName),
							MakeInequalityTestSqlPart(updateFields, stagingName, targetName));

			// get a readable record set from the staging table
			cmd.CommandType = CommandType.Text;
			SqlCeResultSet rstStaging = cmd.ExecuteResultSet(ResultSetOptions.None);
			
			int pkeyCount =  pkeys.GetLength(0); //premature optimisation!
			// Iterate through the results
			// both datasets should be in sync as they are the same set of pkeys in the same order.
			while (rstTarget.Read())
			{
				rstStaging.Read();
				TransferData(rstTarget, rstStaging, updateFields, pkeyCount);
				rstTarget.Update();
			}
			rstStaging.Close();
			rstTarget.Close();
			cmd.Dispose();
		}

		// update data in target record set from staging record set
		private void TransferData(SqlCeResultSet rstTarget, SqlCeResultSet rstStaging, string[] updateFields, int pkeyCount)
		{
			for (int idx = 0; idx < updateFields.GetLength(0); ++idx)
			{
				rstTarget.SetValue(idx + pkeyCount, rstStaging[updateFields[idx]]);
			}
		}

		private String MakeInequalityTestSqlPart(string[] updateFields, string targetName, string stagingName)
		{
			List<String> clauses = new List<string>();
			foreach (String field in updateFields)
				clauses.Add(String.Format("({0}.{2} != {1}.{2} or COALESCE({0}.{2}, {1}.{2}) is not NULL)", Helper.SafeTableName(targetName), Helper.SafeTableName(stagingName), Helper.SafeSqlField(field)));
			return String.Join(" or ", clauses.ToArray());
		}

		private String MakeJoinClauseSqlPart(string[] pkeys, string targetName, string stagingName)
		{
			List<String> clauses = new List<string>();
			foreach (String field in pkeys)
				clauses.Add(String.Format("{0}.{2} = {1}.{2}", Helper.SafeTableName(targetName), Helper.SafeTableName(stagingName),  Helper.SafeSqlField(field)));
			return String.Join(" and ", clauses.ToArray());
		}

		private String MakeUpdateListSqlPart(string[] updateFields, string targetName)
		{
			List<String> clauses = new List<string>();
			foreach (String field in updateFields)
				clauses.Add(String.Format("{0}.{1}", Helper.SafeTableName(targetName),  Helper.SafeSqlField(field)));
			return String.Join(", ", clauses.ToArray());
		}

		private String MakePKListSqlPart(string[] pkeys, string targetName)
		{
			List<String> clauses = new List<string>();
			foreach (String field in pkeys)
				clauses.Add(String.Format("{0}.{1}", Helper.SafeTableName(targetName),  Helper.SafeSqlField(field)));
			return String.Join(", ", clauses.ToArray());
		}

		private SqlCeConnection HeldConnection {get; set; }
		private SqlCeDataReader DataReader {get; set; }

		public override void ReadInit(String connectionString, String sql)
		{
			HeldConnection = new SqlCeConnection(connectionString);
			SqlCeCommand cmd  = new SqlCeCommand(sql, HeldConnection);
			// open the connection
			HeldConnection.Open();
			// 1. get an instance of the SqlDataReader
			DataReader = cmd.ExecuteReader();
		}
		
		// read the data until exhausted
		public override bool ReadNext(out object[] data)
		{
			data = new object[DataReader.FieldCount];
			bool readData = DataReader.Read();
			if (readData)
			{
			    DataReader.GetValues(data);
			}
			return readData;
		}
		// finish
		public override void ReadClose()
		{
			DataReader.Close();
			HeldConnection.Close();
		}		
	}
}
